package com.jimet.objects;

import java.lang.reflect.Method;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Vector;

import com.jimet.repository.JimetRepositoryConnection;

public class JimetDB extends JimetRepositoryConnection {
	protected LinkedHashMap<String, String> dbFieldProp;
	
	protected String tableName;

	
	public JimetDB() throws Exception  {
		super();
	}

	public ResultSet loadById(long id ) throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
		
		String fieldNamesStr = "";
		
		Iterator<String> iter = dbFieldProp.keySet().iterator();
		while(iter.hasNext())
		{
			String key = iter.next();
			fieldNamesStr += tableName+"."+dbFieldProp.get(key) + " AS " + key +  ",";
		}
		
		fieldNamesStr = fieldNamesStr.substring(0,fieldNamesStr.lastIndexOf(",")); 
		
		String queryStr = 	
			"SELECT " + fieldNamesStr +
			" FROM " + tableName + 
			" WHERE "+ dbFieldProp.get("id") +" != -1 ";
			if(dbFieldProp.get("deleted")!=null)
				queryStr +=" AND "+ dbFieldProp.get("deleted") +" != 1 ";
			queryStr +=" AND "+ dbFieldProp.get("id") +" = ?";
		
			
		stmt = connection.prepareStatement(queryStr);
		stmt.setLong(1, id);
		rs = stmt.executeQuery();
		return rs;
	}
	
	public long add(Object anObject) throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
		connection.setAutoCommit(false);

		TemplateUtil util = new TemplateUtil();

		String fieldNamesStr = "";
		String settersStr = "";
		
		Class<?> wrapperClass = anObject.getClass();
		
		Iterator<String> iter = dbFieldProp.keySet().iterator();
		while(iter.hasNext())
		{
			String key = iter.next();
			
			if(key=="id") continue;
			
			fieldNamesStr += dbFieldProp.get(key)+",";
			settersStr += "?,";
		}
		
		fieldNamesStr = fieldNamesStr.substring(0,fieldNamesStr.lastIndexOf(",")); 
		settersStr = settersStr.substring(0,settersStr.lastIndexOf(",")); 

		
		
		String queryStr = 	
			"INSERT INTO " + tableName + " ( "+fieldNamesStr+" ) VALUES ( "+settersStr+" )";
		
			
		stmt = connection.prepareStatement(queryStr);
		iter = dbFieldProp.keySet().iterator();
		int stmtIdx = 1;

		while(iter.hasNext())
		{
			
			String key = iter.next();
			if(key=="id") continue;
			
			Method mt = wrapperClass.getMethod("get"+util.getFirstCaseUpper(key));
			
			stmt.setObject(stmtIdx++,mt.invoke(anObject));
		}

		long id = -1;
		try
		{
			stmt.executeUpdate();
			rs = connection.prepareStatement("SELECT LAST_INSERT_ID()").executeQuery();
			rs.next();
			id = rs.getInt(1);		
			connection.commit();
			connection.setAutoCommit(true);
		}
		catch(Exception ex){
			System.out.println(ex.getMessage());
			connection.rollback();
			connection.setAutoCommit(true);
			return id;
		}
		return id;
	}
	
	public long update(Object anObject) throws Exception
	{
		PreparedStatement stmt;
		connection.setAutoCommit(false);

		TemplateUtil util = new TemplateUtil();

		String fieldNamesStr = "";
		
		Class<?> wrapperClass = anObject.getClass();
		
		Iterator<String> iter = dbFieldProp.keySet().iterator();
		while(iter.hasNext())
		{
			String key = iter.next();
			
			if(key=="id") continue;
			
			fieldNamesStr += dbFieldProp.get(key)+"=?,";
		}
		
		fieldNamesStr = fieldNamesStr.substring(0,fieldNamesStr.lastIndexOf(",")); 
		
		Method getId = wrapperClass.getMethod("getId");
		
		String queryStr = 	
			"UPDATE " + tableName + " SET "+fieldNamesStr+" WHERE " + dbFieldProp.get("id") + "=" + getId.invoke(anObject);
		
			
		stmt = connection.prepareStatement(queryStr);
		iter = dbFieldProp.keySet().iterator();
		int stmtIdx = 1;

		while(iter.hasNext())
		{
			
			String key = iter.next();
			if(key=="id") continue;
			
			Method mt = wrapperClass.getMethod("get"+util.getFirstCaseUpper(key));
			
			stmt.setObject(stmtIdx++,mt.invoke(anObject));
		}

		try
		{
			stmt.executeUpdate();
			connection.commit();
			connection.setAutoCommit(true);
			return Long.parseLong(getId.invoke(anObject).toString());
		}
		catch(Exception ex){
			System.out.println(ex.getMessage());
			connection.rollback();
			connection.setAutoCommit(true);
			return -1;
		}
	}

	public boolean delete(long id) throws Exception
	{
		
		PreparedStatement stmt;
		
		String queryStr = 
			"DELETE FROM " + tableName +
			" WHERE " + dbFieldProp.get("id") + "=?";
			
		stmt = connection.prepareStatement(queryStr);
		stmt.setLong(1,id);
		
		try{
			stmt.executeUpdate();
		}
		catch(Exception ex){
			System.out.println(ex.getMessage());
			return false;
		}
		return true;
	}
	
	public long addBatch(JimetObjects objects) throws Exception
	{
		if(objects.size()==0)
			return 1;
		
		PreparedStatement stmt;
		ResultSet rs;
		
		connection.setAutoCommit(false);
				
		TemplateUtil util = new TemplateUtil();

		String fieldNamesStr = "";
		String settersStr = "";
		
		Method mt = objects.getClass().getMethod("getObject");
		Method next = objects.getClass().getMethod("next");
		Method getNext = objects.getClass().getMethod("getNext");
		
		Object objectsObjects = mt.invoke(objects);

		Class<?> wrapperClass = (Class<?>)objectsObjects;
		
		Iterator<String> iter = dbFieldProp.keySet().iterator();
		while(iter.hasNext())
		{
			String key = iter.next();
			
			if(key=="id") continue;
			
			fieldNamesStr += dbFieldProp.get(key)+",";
			settersStr += "?,";
		}
		
		fieldNamesStr = fieldNamesStr.substring(0,fieldNamesStr.lastIndexOf(",")); 
		settersStr = settersStr.substring(0,settersStr.lastIndexOf(",")); 

		String queryStr = 	
			"INSERT INTO " + tableName + " ( "+fieldNamesStr+" ) VALUES ( "+settersStr+" )";
	
			stmt = connection.prepareStatement(queryStr);
			while ((Boolean) next.invoke(objects)) {
				
				Object obj = getNext.invoke(objects);
				
				int stmtIdx = 1;
				
				iter = dbFieldProp.keySet().iterator();
				while(iter.hasNext())
				{
					
					String key = iter.next();
					if(key=="id") continue;
					
					Method mt1 = wrapperClass.getMethod("get"+util.getFirstCaseUpper(key));
					
					stmt.setObject(stmtIdx++,mt1.invoke(obj));
				}
				
				stmt.addBatch();
			}
		

		long id = -1;
		try
		{
			stmt.executeBatch();
			rs = connection.prepareStatement("SELECT LAST_INSERT_ID()").executeQuery();
			rs.next();
			id = rs.getInt(1);		
			connection.commit();
			connection.setAutoCommit(true);
		}
		catch(Exception ex){
			System.out.println(ex.getMessage());
			connection.rollback();
			connection.setAutoCommit(true);
			return id;
		}
		return id;
	}
	
	public long updateBatch(JimetObjects objects) throws Exception
	{
		if(objects.size()==0)
			return 1;
		
		PreparedStatement stmt;
		
		connection.setAutoCommit(false);
	
	
		TemplateUtil util = new TemplateUtil();

		String fieldNamesStr = "";
		
		Method mt = objects.getClass().getMethod("getObject");
		Method next = objects.getClass().getMethod("next");
		Method getNext = objects.getClass().getMethod("getNext");
		
		Object objectsObjects = mt.invoke(objects);

		
		
		Class<?> wrapperClass = (Class<?>)objectsObjects;
		
		Iterator<String> iter = dbFieldProp.keySet().iterator();
		while(iter.hasNext())
		{
			String key = iter.next();
			
			if(key=="id") continue;
			
			fieldNamesStr += dbFieldProp.get(key)+"=?,";
		}
		
		fieldNamesStr = fieldNamesStr.substring(0,fieldNamesStr.lastIndexOf(",")); 
		
		Method getId = wrapperClass.getMethod("getId");
		
		String queryStr = 	
			"UPDATE " + tableName + " SET "+fieldNamesStr+" WHERE " + dbFieldProp.get("id") + "=?";

		stmt = connection.prepareStatement(queryStr);
		long lastStoredId = -1 ;
		
		stmt = connection.prepareStatement(queryStr);
		while ((Boolean) next.invoke(objects)) {
			
			Object obj = getNext.invoke(objects);
			
			int stmtIdx = 1;
			
			iter = dbFieldProp.keySet().iterator();
			while(iter.hasNext())
			{
				
				String key = iter.next();
				if(key=="id") continue;
				
				Method mt1 = wrapperClass.getMethod("get"+util.getFirstCaseUpper(key));
				
				stmt.setObject(stmtIdx++,mt1.invoke(obj));
			}
			
			stmt.setObject(stmtIdx++, getId.invoke(obj));
			
			lastStoredId = Long.parseLong( getId.invoke(obj).toString() );
			
			stmt.addBatch();
		}
		try{
			stmt.executeBatch();			
			connection.commit();
			connection.setAutoCommit(true);
			return lastStoredId;
		}
		catch(Exception ex){
			System.out.println(ex.getMessage());
			connection.rollback();
			connection.setAutoCommit(true);			
			return -1;
		}
			
	}

	public boolean deleteBatch(JimetObjects objects) throws Exception
	{
		if(objects.size()==0)
			return true;
		
		PreparedStatement stmt;
		
		connection.setAutoCommit(false);
	
	
		
		Method mt = objects.getClass().getMethod("getObject");
		Method next = objects.getClass().getMethod("next");
		Method getNext = objects.getClass().getMethod("getNext");
		
		Object objectsObjects = mt.invoke(objects);

		
		
		Class<?> wrapperClass = (Class<?>)objectsObjects;
		Method getId = wrapperClass.getMethod("getId");
		
		String queryStr = 	
			"DELETE FROM " + tableName + " WHERE " + dbFieldProp.get("id") + "=?";

		stmt = connection.prepareStatement(queryStr);
		
		stmt = connection.prepareStatement(queryStr);
		while ((Boolean) next.invoke(objects)) {
			
			Object obj = getNext.invoke(objects);
			
			int stmtIdx = 1;
			stmt.setObject(stmtIdx++,getId.invoke(obj));
			stmt.addBatch();
		}
		try{
			stmt.executeBatch();			
			connection.commit();
			connection.setAutoCommit(true);
			return true;
		}
		catch(Exception ex){
			System.out.println(ex.getMessage());
			connection.rollback();
			connection.setAutoCommit(true);			
			return false;
		}
			
	}
	
	public ResultSet getResultSetByFieldsWithExpr(HashMap<Object,Object> fieldValueProp, String strSort, String strOrderBy,long startIndex, long endIndex) throws Exception
	{
		ResultSet rs	= this.getResultSetByFieldsWithExpr(fieldValueProp, "AND" , strSort, strOrderBy, startIndex, endIndex);
		return rs;
	}
	
	public ResultSet getResultSetByFieldsWithExpr(HashMap<Object,Object> fieldValueProp, Vector<String> sortVect, Vector<String> orderByVect,long startIndex, long endIndex) throws Exception
	{
		ResultSet rs	= this.getResultSetByFieldsWithExpr(fieldValueProp, "AND" , sortVect, orderByVect, startIndex, endIndex);
		return rs;
	}
	
	public ResultSet getResultSetByFieldsWithExpr(HashMap<Object,Object> fieldValueProp, String operator ,String strSort, String strOrderBy,long startIndex, long endIndex) throws Exception
	{
		String strProps = " ";

		for (Map.Entry<Object, Object> entry: fieldValueProp.entrySet()) 
		{
			Object key = entry.getKey();
			Object value = entry.getValue();
		
			if(key.getClass().getName().equals("java.lang.String")){
			
				String[] valueArr = ((String)value).split("#T#");
				for(int i=0;i<valueArr.length;i++){
					strProps += " "+ this.dbFieldProp.get(key) + " " +valueArr[i] + " "+operator;
				}
			}			
			else
			{
				
				System.out.println(((JimetDB)(((JimetObject)((Class<?>) key).newInstance()).getDb().newInstance())).getDbFieldProp());
				JimetDB foreingDB = ((JimetDB)(((JimetObject)((Class<?>) key).newInstance()).getDb().newInstance()));
				LinkedHashMap<String, String> foreingMap = foreingDB.getDbFieldProp();
				
				strProps += (" EXISTS ( SELECT "+foreingMap.get("id")+" FROM "+foreingDB.getTableName()+" WHERE "+foreingMap.get("id")+" = "+tableName+"."+dbFieldProp.get( ((String[]) value)[0]+"Id" )+" AND "+foreingMap.get(((String[]) value)[1])+" "+((String[]) value)[2]+" ) AND");
				
			}
		}

		if(fieldValueProp.size()>0) {
			strProps = strProps.substring(0,strProps.length()-operator.length());
			strProps = " AND ( " + strProps + " ) ";
		}

		
		String fieldNamesStr = "";
		
		Iterator<String> iter = dbFieldProp.keySet().iterator();
		while(iter.hasNext())
		{
			String key = iter.next();
			if ( dbFieldProp.get(key).contains("UPPER(REPLACE(") )
			{
				String tmpStr	= "UPPER(REPLACE(";
				tmpStr			+= tableName+".";
				tmpStr			+= dbFieldProp.get(key).substring(14);
				fieldNamesStr 	+= tmpStr + " AS " + key +  ",";
			}
			else
				fieldNamesStr += tableName+"."+dbFieldProp.get(key) + " AS " + key +  ",";
		}
		
		fieldNamesStr = fieldNamesStr.substring(0,fieldNamesStr.lastIndexOf(",")); 
		

		ResultSet rs;
		PreparedStatement stmt;
	
		String queryStr = "";
		if ( startIndex == -1 && endIndex == -1 ){
			queryStr = 
				"SELECT " + fieldNamesStr +
				" FROM "+ tableName +
				" WHERE "+ dbFieldProp.get("id") +" != -1 " + strProps;
				if(dbFieldProp.get("deleted")!=null)
					queryStr +=" AND "+ dbFieldProp.get("deleted") +" != 1 ";
				queryStr += " ORDER BY " + dbFieldProp.get(strOrderBy) + " " + strSort;
		}
		else{
			queryStr = 
				"SELECT " + fieldNamesStr +
				" FROM "+ tableName +
				" WHERE "+ dbFieldProp.get("id") +" != -1 " + strProps;
				if(dbFieldProp.get("deleted")!=null)
					queryStr +=" AND "+ dbFieldProp.get("deleted") +" != 1 ";
				queryStr += " ORDER BY " + dbFieldProp.get(strOrderBy) + " " + strSort;
				queryStr += " LIMIT ?,?";
		}
		
		stmt = connection.prepareStatement(queryStr);
		if (! (startIndex == -1 && endIndex == -1) ){
			stmt.setLong(1,startIndex);
			stmt.setLong(2,endIndex);		
		}
		rs = stmt.executeQuery();		
		return rs;
	}

	public ResultSet getResultSetByFieldsWithExpr(HashMap<Object,Object> fieldValueProp, String operator , Vector<String> sortVect, Vector<String> orderByVect,long startIndex, long endIndex) throws Exception
	{
		String strProps = " ";

		for (Map.Entry<Object, Object> entry: fieldValueProp.entrySet()) 
		{
			Object key = entry.getKey();
			Object value = entry.getValue();
		
			if(key.getClass().getName().equals("java.lang.String")){
			
				String[] valueArr = ((String)value).split("#T#");
				for(int i=0;i<valueArr.length;i++){
					strProps += " "+ this.dbFieldProp.get(key) + " " +valueArr[i] + " "+operator;
				}
			}			
			else
			{
				
				System.out.println(((JimetDB)(((JimetObject)((Class<?>) key).newInstance()).getDb().newInstance())).getDbFieldProp());
				JimetDB foreingDB = ((JimetDB)(((JimetObject)((Class<?>) key).newInstance()).getDb().newInstance()));
				LinkedHashMap<String, String> foreingMap = foreingDB.getDbFieldProp();
				
				strProps += (" EXISTS ( SELECT "+foreingMap.get("id")+" FROM "+foreingDB.getTableName()+" WHERE "+foreingMap.get("id")+" = "+tableName+"."+dbFieldProp.get( ((String[]) value)[0]+"Id" )+" AND "+foreingMap.get(((String[]) value)[1])+" "+((String[]) value)[2]+" ) AND");
				
			}
		}

		if(fieldValueProp.size()>0) {
			strProps = strProps.substring(0,strProps.length()-operator.length());
			strProps = " AND ( " + strProps + " ) ";
		}

		
		String fieldNamesStr = "";
		
		Iterator<String> iter = dbFieldProp.keySet().iterator();
		while(iter.hasNext())
		{
			String key = iter.next();
			fieldNamesStr += tableName+"."+dbFieldProp.get(key) + " AS " + key +  ",";
		}
		
		fieldNamesStr = fieldNamesStr.substring(0,fieldNamesStr.lastIndexOf(",")); 
		

		ResultSet rs;
		PreparedStatement stmt;
		

		String strSortLine	= "";
		
		for ( int i = 0 ; i<orderByVect.size(); i++ )
		{
			String tmpOrderBy	= dbFieldProp.get(orderByVect.get(i));
			String tmpSort		= sortVect.get(i);
			strSortLine			+= tmpOrderBy + " " + tmpSort + " , ";
		}
		if ( strSortLine.length() >= 3 )
			strSortLine			= strSortLine.substring(0 , strSortLine.length()-3 );
		
	
		String queryStr = "";
		if ( startIndex == -1 && endIndex == -1 ){
			queryStr = 
				"SELECT " + fieldNamesStr +
				" FROM "+ tableName +
				" WHERE "+ dbFieldProp.get("id") +" != -1 " + strProps;
				if(dbFieldProp.get("deleted")!=null)
					queryStr +=" AND "+ dbFieldProp.get("deleted") +" != 1 ";
				queryStr += " ORDER BY " + strSortLine;
		}
		else{
			queryStr = 
				"SELECT " + fieldNamesStr +
				" FROM "+ tableName +
				" WHERE "+ dbFieldProp.get("id") +" != -1 " + strProps;
				if(dbFieldProp.get("deleted")!=null)
					queryStr +=" AND "+ dbFieldProp.get("deleted") +" != 1 ";
				queryStr += " ORDER BY " + strSortLine;
				queryStr += " LIMIT ?,?";
		}
		
		stmt = connection.prepareStatement(queryStr);
		if (! (startIndex == -1 && endIndex == -1) ){
			stmt.setLong(1,startIndex);
			stmt.setLong(2,endIndex);		
		}
		rs = stmt.executeQuery();		
		return rs;
}

	
	public ResultSet loadByIds(String [] strArrIDs, String strSort, String strOrderBy,int startIndex, int endIndex) throws Exception
	{
		String strIDs = "(";
		for (int i=0;i<strArrIDs.length;i++)
			strIDs += strArrIDs[i] + ",";
		
		strIDs = strIDs.substring(0,strIDs.length()-1);
		strIDs += " )";

		
		String fieldNamesStr = "";
		
		Iterator<String> iter = dbFieldProp.keySet().iterator();
		while(iter.hasNext())
		{
			String key = iter.next();
			fieldNamesStr += tableName+"."+dbFieldProp.get(key) + " AS " + key +  ",";
		}
		
		fieldNamesStr = fieldNamesStr.substring(0,fieldNamesStr.lastIndexOf(",")); 
		

		ResultSet rs;
		PreparedStatement stmt;
	
		String queryStr = "";
		if ( startIndex == -1 && endIndex == -1 ){
			queryStr = 
				"SELECT " + fieldNamesStr +
				" FROM "+ tableName +
				" WHERE "+ dbFieldProp.get("id") +" != -1 AND "+ dbFieldProp.get("id") + " IN " + strIDs;
				if(dbFieldProp.get("deleted")!=null)
					queryStr +=" AND "+ dbFieldProp.get("deleted") +" != 1 ";
				queryStr += " ORDER BY " + dbFieldProp.get(strOrderBy) + " " + strSort;
		}
		else{
			queryStr = 
				"SELECT " + fieldNamesStr +
				" FROM "+ tableName +
				" WHERE "+ dbFieldProp.get("id") +" != -1 AND "+ dbFieldProp.get("id") + " IN " + strIDs;
				if(dbFieldProp.get("deleted")!=null)
					queryStr +=" AND "+ dbFieldProp.get("deleted") +" != 1 ";
				queryStr += " ORDER BY " + dbFieldProp.get(strOrderBy) + " " + strSort;
				queryStr += " LIMIT ?,?";
		}
		
		stmt = connection.prepareStatement(queryStr);
		if (! (startIndex == -1 && endIndex == -1) ){
			stmt.setInt(1,startIndex);
			stmt.setInt(2,endIndex);		
		}
		rs = stmt.executeQuery();		
		return rs;
}
	
	public ResultSet getCount(HashMap<Object,Object> fieldValueProp) throws Exception
	{
		String strProps = " ";
		
		for (Map.Entry<Object, Object> entry: fieldValueProp.entrySet()) 
		{
			Object key = entry.getKey();
			Object value = entry.getValue();
		
			if(key.getClass().getName().equals("java.lang.String"))
			{
				String[] valueArr = ((String)value).split("#T#");
				for(int i=0;i<valueArr.length;i++){
					strProps += " "+ this.dbFieldProp.get(key)+ " " +valueArr[i] + " AND";
				}
				
			}
			else
			{
				
				System.out.println(((JimetDB)(((JimetObject)((Class<?>) key).newInstance()).getDb().newInstance())).getDbFieldProp());
				JimetDB foreingDB = ((JimetDB)(((JimetObject)((Class<?>) key).newInstance()).getDb().newInstance()));
				LinkedHashMap<String, String> foreingMap = foreingDB.getDbFieldProp();
				
				strProps += (" EXISTS ( SELECT "+foreingMap.get("id")+" FROM "+foreingDB.getTableName()+" WHERE "+foreingMap.get("id")+" = "+tableName+"."+dbFieldProp.get( ((String[]) value)[0]+"Id" )+" AND "+foreingMap.get(((String[]) value)[1])+" "+((String[]) value)[2]+" ) AND");
				
			}
		}

		if(fieldValueProp.size()>0) {
			strProps = strProps.substring(0,strProps.length()-3);
			strProps = " AND " + strProps;
		}

		
		String fieldNamesStr = "";
		
		Iterator<String> iter = dbFieldProp.keySet().iterator();
		while(iter.hasNext())
		{
			String key = iter.next();
			fieldNamesStr += tableName+"."+dbFieldProp.get(key) + " AS " + key +  ",";
		}
		
		fieldNamesStr = fieldNamesStr.substring(0,fieldNamesStr.lastIndexOf(",")); 
		

		ResultSet rs;
		PreparedStatement stmt;
	
		String queryStr = "";
			queryStr = 
				"SELECT " +
				" COUNT("+dbFieldProp.get("id")+") " + 
				" FROM "+ tableName +
				" WHERE "+ dbFieldProp.get("id") +" != -1 " + strProps;
				if(dbFieldProp.get("deleted")!=null)
					queryStr +=" AND "+ dbFieldProp.get("deleted") +" != 1 ";
		
		stmt = connection.prepareStatement(queryStr);
		rs = stmt.executeQuery();		
		return rs;
	}
	
	public LinkedHashMap<String, String> getDbFieldProp() {
		return dbFieldProp;
	}

	public String getTableName() {
		return tableName;
	}

	public void setTableName(String tableName) {
		this.tableName = tableName;
	}

}
